H2 Database

at the moment when you look at the PhotozService you can see that it has a HashMap and it stores the photos inside the memory, but we want to access a database
as mentioned in the beginning we added a h2 database
an h2 database is just a java library and once you add it to your project Spring Boot will automatically boot it up when you start Spring Boot
by default, you will not see the database, so there will not be any files or whatever, but you can use it as you would use eg. Postgres or Oracle MySQL or Microsoft SQL Server

Step 1: Set up the H2 Database

by default, the database is only available in memory, but you can tell h2 or Spring to save the database contents to a file so you can actually see it and have the impression that something is saved at least
you need to tell Spring where to save the database file, so in order to save it under ~/springboot add:

application.properties
spring.application.name=photoz-clone
spring.servlet.multipart.max-file-size=100MB
spring.servlet.multipart.max-request-size=100MB
spring.datasource.url=jdbc:h2:file:~/springboot;AUTO_SERVER=TRUE;

common application properties
h2 database cheatSheet
now when you rerun the app the database will be created which you can see in a message like HikariPool-1 - Added connection conn1: url=jdbc:h2:file:~/springboot user=
- check this by searching ~/springboot
- you will find two files: springboot.lock.db and springboot.mv.db
  - you can copy these files to any server and reuse them (just like sqlite, but you only need java for it to run)

Step 2: Schema Creation

we want to store our photos in the database which means we need some tables
Spring has various ways of checking if there are some tables already in the database when you boot up Spring Boot and if not it creates them: options are Liquibase and Flyway which are more full-blown options and we will not cover them now, but instead we will use the most basic Spring database migration tools available
- see wikipedia: database migration (aka schema migration)
  - "Schema migration tools can be said to solve versioning problems for database schemas just as version control systems solve versioning problems for source code."
    - phth: in other words, "database migration" is version control for database schemas, so Liquibase and Flyway are a bit like git
- see wikipedia: Flyway
- see wikipedia: Liquibase
create src/main/resources/schema.sql with the following content:
- identity means it is auto-generated
- essentially, we are just mapping our Photo class to a database table
  - wikipedia: ORM
  - wikipedia: Hibernate
    - "Hibernate ORM (or simply Hibernate) is an object–relational mapping tool for the Java programming language."
  - Introduction to ORM with Spring
    - "The Spring Framework supports integration with the Java Persistence API (JPA) and supports native Hibernate for resource management, data access object (DAO) implementations, and transaction strategies."
    - "You can configure all of the supported features for OR (object relational) mapping tools through Dependency Injection."
  - Prisma ORM (very popular)
    - Fireship: Prisma
  - Sequelize ORM
  - Drizzle ORM
    - Fireship: Drizzle
- depending on your RDBMS (Postgres, MySQL) you may have slightly different table creation statements, but if you are following along the code below should work

tutorial was made with a different h2 database version

Instead of

schema.sql (wrong version)
create table if not exists photoz (
    id bigint identity primary key,
    file_name varchar(255),
    content_type varchar(255),
    data binary
)

you have to use

schema.sql
create table if not exists photoz (
    id int primary key not null auto_increment,
    filename varchar(255),
    content_type varchar(255),
    data binary large object
)

Warning: This is a phth version and not the YouTube comment section version (because I used filename instead of fileName as data member of the Photo class - this will be important later when mapping the data members to the SQL table columns)!

we will need one additional application property:
- there are specific configurations like "in production" (aka "prod") or whatever when Spring does not execute the schema.sql files, but now I want the schema.sql to always be executed whenever we boot it up

application.properties
spring.application.name=photoz-clone
spring.servlet.multipart.max-file-size=100MB
spring.servlet.multipart.max-request-size=100MB
spring.datasource.url=jdbc:h2:file:~/springboot;AUTO_SERVER=TRUE;
spring.sql.init.mode=always

rerun the app
- we get an error message Syntax error in SQL statement "create table if not exists photoz {[*]" [42000-224]
- here, we could make use of IntelliJ features to see what the syntax error is, but SQL features are only supported in IntelliJ Ultimate
- the "poor man's version" is this:
  - the h2 database comes with a web interface, you only have to enable it
  - you do this by adding a new application property:

application.properties
spring.application.name=photoz-clone
spring.servlet.multipart.max-file-size=100MB
spring.servlet.multipart.max-request-size=100MB
spring.datasource.url=jdbc:h2:file:~/springboot;AUTO_SERVER=TRUE;
spring.sql.init.mode=always
spring.h2.console.enabled=true

for testing purposes, rename the file schema.sql to bacschema.sql (F2) so that it is not executed
copy the content of bacschema.sql
rerun the app
- you will see a message like H2 console available at '/h2-console'. Database available at 'jdbc:h2:file:~/springboot' which is the web interface we wanted
- open http://localhost:8080/h2-console and you will see the h2 web interface
- enter the JDBC URL jdbc:h2:~/springboot, no User Name and no Password (because we did not set any)
- click on "Connect"
- a new view will open where you can see the INFORMATION_SCHEMA and a single empty user under Users on the left side of the view
- in the white box we can now execute our SQL statement
- so paste the copied SQL statement in there and click the "Run" button to see what's wrong
  - this works, so the statement itself has to be correct
  - so drop the table again with drop table photoz;
double checking bacschema.sql: are we missing a semicolon?
- rename bacschema.sql to schema.sql (F2)
- rerun the app
  - now the app starts without error messages
  - so it was just the semicolon at the end
- now run select * from photoz; in the web interface
  - we get no rows back at the moment, but that is what we will change next

Step 3: Connecting the REST API to the Database

the easiest way to get started with databases is Spring Data JDBC

"JDBC" vs "Spring Data JDBC"

JDBC is the low-level API to send database statements from java to the database
Spring Data JDBC is a tiny convenience wrapper around this jdbc API - and you do not have to read ten books like you have to with JPA and Hibernate, even though you think you might not have to! You can get started rather easily.

Spring Data is its all own concept essentially, but we will give here a tiny quick start again

3.1: Create a Spring Data Repository

Spring Data wants you to create so-called repositories, so we create a class repository.PhotozRepository

tip

When you create a new class (altinsert) that has a name like repository.PhotozRepository, IntelliJ will automatically create a repository folder and a PhotozRepository class inside the repository folder in one go.

this class should be an interface and it extends an already existing interface that comes from Spring Data which is called CrudRepository
- the CrudRepository gives you a ton of methods like findAll, delete, save into a database without you having to write them
- you can write your own SQL statements, but for now we just want to get started with the CrudRepository and get the basic findAll, findById and update statements working
- we need to specify template arguments for the template parameters of the CrudRepository template which have to be CrudRepository<Photo, Integer> for our specific database (you can check the template signature by using ctrlshiftspace)
  PhotozRepository.java
```
package com.companyname.phth.photoz.clone.repository;

import com.companyname.phth.photoz.clone.model.Photo;
import org.springframework.data.repository.CrudRepository;

public interface PhotozRepository extends CrudRepository<Photo, Integer> {
}
```
- when you click on CrudRepository while holding ctrl you will see that you have save(), findAll(), existsById(), count() and all the other methods. They are @Transactional by default which is a different big topic. You can read about that here:
  - Transactional
- in short, when Spring boots up it scans the repository, finds that there are CrudRepository(s) and generates the SQL statements for you so that the database calls work

3.2: Map the Java Class to the Database Table

next, we have to specify some marker annotations to map our java class to the database table (see "ORM" links above)
- @Table("PHOTOZ") on the Photo class (we use the uppercase PHOTOZ here because h2 will convert all tables by default to uppercase) tells Spring that Photos live in the PHOTOZ table
- then we need to tell Spring what the id field of the table is by using @Id on the class data member id which is not going to be a String any more, but an auto-generated Integer
- next we can delete the constructor public Photo(String id, String filename), let's find out where it is being used ...
- by default, camelcase names such as String fileName (phth: I used filename) and String contentType are converted to file_name and content_type by Spring and we actually have that already in our schema.sql file so that should be working
- data is also fine
- we also need to fix the methods that use id so that they use id's new type Integer instead of String
  Photo.java
```
public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}
```

3.3: Inject the `PhotozRepository` into the `PhotozService`

let's see what else we need to fix:
- we need to change PhotozService: the PhotozService - and that is the nice thing about having the dependency injection working - is now encapsulated so that all the other classes do not have to be changed and we just need to fix the PhotozService
- first, we need to give the PhotozService a PhotozRepository, so we replace the HashMap db with private PhotozRepository photozRepository; in PhotozService.java - it should be automatically injected (see "constructor injection" discussion above) - and we add a new PhotozService constructor that has a PhotozRepository parameter (by using IntelliJ context actions ctrl.)
  Photo.java
```
private final PhotozRepository photozRepository;

public PhotozService(PhotozRepository photozRepository) {
    this.photozRepository = photozRepository;
}
```

3.4: Replace all `db` calls with `PhotozRepository` calls

then, we replace all references to db with photozRepository calls like so:
- get(): Spring Data methods return Iterables, so just change the return type of get() to Iterable<Photo>
  Photo.java
```
public Iterable<Photo> get() {
    return photozRepository.findAll();
}
```
- get(String id): we have to append a .orElse(null) because the method .findById() returns an Optional
  - java: Optional orElse() method
    - "The orElse() method of java.util.Optional class in Java is used to get the value of this Optional instance, if present. If there is no value present in this Optional instance, then this method returns the specified value."
  - java: when to use Optional?: "The main design goal of Optional is to provide a means for a function returning a value to indicate the absence of a return value."
  Photo.java
```
public Photo get(Integer id) {
    return photozRepository.findById(id).orElse(null);
}
```
- remove(String id): we will not return a Photo any more because deleteById() does not return a Photo
  Photo.java
```
public void remove(Integer id) {
    photozRepository.deleteById(id);
}
```
- save(...): we are not setting an id any more, that should come from the database being automatically generated
  Photo.java
```
public Photo save(String fileName, String contentType, byte[] data) {
    Photo photo = new Photo();
    photo.setFilename(fileName);
    photo.setContentType(contentType);
    photo.setData(data);
    photozRepository.save(photo);
    return photo;
}
```
summarizing, we have

Photo.java
package com.companyname.phth.photoz.clone.service;

import com.companyname.phth.photoz.clone.model.Photo;
import com.companyname.phth.photoz.clone.repository.PhotozRepository;
import org.springframework.stereotype.Service;

import java.util.UUID;

//@Component
@Service
public class PhotozService {
    private final PhotozRepository photozRepository;

    public PhotozService(PhotozRepository photozRepository) {
        this.photozRepository = photozRepository;
    }

    public Iterable<Photo> get() {
        return photozRepository.findAll();
    }

    public Photo get(Integer id) {
        return photozRepository.findById(id).orElse(null);
    }

    public void remove(Integer id) {
        return photozRepository.deleteById(id);
    }

    public Photo save(String fileName, String contentType, byte[] data) {
        Photo photo = new Photo();
        photo.setFilename(fileName);
        photo.setContentType(contentType);
        photo.setData(data);
        photozRepository.save(photo);
        return photo;
    }

}

3.5: Change the type of `id` from `String` to `Integer` everywhere

next, we fix the errors in PhotozController:
- the only reason we have to change the PhotozController is because we have changed the type of id from String to Integer
- get(): change the return type to Iterable
- get(id): type of id must be Integer
- delete(id): type of id must be Integer; delete(id) should not return any HTTP status code any more (we will always give back a 200 HTTP status code and do not worry about whether the photo exists or not)

PhotozController.java
package com.companyname.phth.photoz.clone.web;

import com.companyname.phth.photoz.clone.model.Photo;
import com.companyname.phth.photoz.clone.service.PhotozService;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.server.ResponseStatusException;

import java.io.IOException;
import java.util.Collection;

@RestController
public class PhotozController {

    private final PhotozService photozService;

    public PhotozController(PhotozService photozService) {
        this.photozService = photozService;
    }

    @GetMapping("/")
    public String hello() {
        return "Hello World!";
    }

    @GetMapping("/photoz")
    public Iterable<Photo> get() {
        return photozService.get();
    }

    @GetMapping("/photoz/{id}")
    public Photo get(@PathVariable Integer id) {
        Photo photo = photozService.get(id);
        if (photo == null) throw new ResponseStatusException(HttpStatus.NOT_FOUND);
        return photo;
    }

    @DeleteMapping("/photoz/{id}")
    public void delete(@PathVariable Integer id) {
        photozService.remove(id);
    }

    @PostMapping("/photoz")
    public Photo create(@RequestPart("data") MultipartFile file) throws IOException {
        return photozService.save(file.getOriginalFilename(), file.getContentType(), file.getBytes());
    }
}

we need to change the type of id in the DownloadController as well:

DownloadController.java
// imports

@RestController
public class DownloadController {
    private final PhotozService photozService;

    public DownloadController(PhotozService photozService) {
        this.photozService = photozService;
    }

    @GetMapping("/download/{id}")
    public ResponseEntity<byte[]> download(@PathVariable Integer id) {

        Photo photo = photozService.get(id);

        // ...

        return new ResponseEntity<>(data, headers, HttpStatus.OK);
    }
}

3.6: Run the App

rerun the app
localhost:8080: shows Hello World!
http://localhost:8080/photoz: shows an empty list of photos (phth: When it shows a "Whitelabel Error Page" page then something is wrong. Make sure you DROP the PHOTOZ table each time you rerun the app because the app does not delete the PHOTOZ table before each rerun! This can cause a lot of confusion when fixing your code.)
http://localhost:8080/upload.html: upload a file, this works
- http://localhost:8080/photoz shows [{"id":1,"filename":"Angela_Merkel_freigestellt_ohneKopf.png","contentType":"image/png"}] now
- http://localhost:8080/h2-console: run select * from photoz;: shows one row in the table PHOTOZ with our uploaded file
- http://localhost:8080/download/1: downloads the uploaded file
- we can also upload multiple different photos and see them as new rows in the table/database

Recap

recap:
- we have connected a database to our REST API
- the frontend/the browser can
  - send files to the REST API,
  - save them to the database and
  - return them again

What's Next?

we ignored a lot of things:
- we have to make sure we have validation
- we have to make sure we sanitize our data
- we have to make sure to limit our data properly
- all the things that are important for a real world app
but here, the most important part is seeing the data in the database at the end

Step 1: Set up the H2 Database​

Step 2: Schema Creation​

Step 3: Connecting the REST API to the Database​

3.1: Create a Spring Data Repository​

3.2: Map the Java Class to the Database Table​

3.3: Inject the PhotozRepository into the PhotozService​

3.4: Replace all db calls with PhotozRepository calls​

3.5: Change the type of id from String to Integer everywhere​

3.6: Run the App​

Recap​

What's Next?​